package com.dfrz.bookstore.dao.impl;

import com.dfrz.bookstore.dao.Dao;
import com.dfrz.bookstore.entry.ShoppingCar;
import com.dfrz.bookstore.util.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 类功能描述:
 *
 * @Author keyan
 * @Date 2021/5/21 18:39
 */
public class ShoppingCarDaoImpl implements Dao<ShoppingCar> {

    @Override
    public boolean insert(ShoppingCar shoppingCar)   {
        String sql = "insert into shoppingcar (user_id,book_id,book_num,book_price,book_name) values (?,?,?,?,?)";
        PreparedStatement prep = null;
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1,shoppingCar.getUserId());
            prep.setInt(2,shoppingCar.getBookId());
            prep.setInt(3,shoppingCar.getBookNum());
            prep.setDouble(4,shoppingCar.getBookPrice());
            prep.setString(5,shoppingCar.getBookName());
            int i = prep.executeUpdate();
            if (i>0){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,prep,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean deleteObjById(int i) {
        String sql = "delete from shoppingcar where id = "+i ;
        Connection conn = null;
        Statement stat;
        try {
            conn = JdbcUtils.getConnection();
            stat = conn.createStatement();
            int i1 = stat.executeUpdate(sql);
            if (i1>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateByObj(ShoppingCar shoppingCar) {
        if (shoppingCar.getId()==0){
            return false;
        }
        StringBuilder sb = new StringBuilder("update shoppingcar set id = "+shoppingCar.getId());
        if (shoppingCar.getUserId()!=0){
            sb.append("  , user_id = "+shoppingCar.getUserId());
        }
        if (shoppingCar.getBookId()!=0){
            sb.append("  , book_id = "+shoppingCar.getBookId());
        }
        if (shoppingCar.getBookNum()!=0){
            sb.append(" , book_num = "+shoppingCar.getBookNum());
        }
        if (shoppingCar.getBookPrice()!=0){
            sb.append(" , book_price = "+shoppingCar.getBookPrice());
        }
        if (shoppingCar.getBookName()!=null){
            sb.append("  , book_name = '"+shoppingCar.getBookName()+"' ");
        }

        sb.append(" where id = "+shoppingCar.getId());
        Connection conn = null;
        Statement stat = null;
        try {
            conn = JdbcUtils.getConnection();
            stat =  conn.createStatement();
            int i = stat.executeUpdate(sb.toString());
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public List<ShoppingCar> findObjByObj(ShoppingCar shoppingCar) {
        List<ShoppingCar> shoppingCarList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from shoppingcar where 1=1 ");
        if (shoppingCar.getId()!= 0){
            sb.append(" and id = "+shoppingCar.getId());
        }
        if (shoppingCar.getUserId()!=0){
            sb.append("  and user_id = "+shoppingCar.getUserId());
        }
        if (shoppingCar.getBookId()!=0){
            sb.append("  and book_id = "+shoppingCar.getBookId());
        }
        if (shoppingCar.getBookNum()!=0){
            sb.append(" and book_num = "+shoppingCar.getBookNum());
        }
        if (shoppingCar.getBookPrice()!=0){
            sb.append(" and book_price = "+shoppingCar.getBookPrice());
        }
        if (shoppingCar.getBookName()!=null){
            sb.append("  and book_name = '%"+shoppingCar.getBookName()+"%' ");
        }
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                ShoppingCar shoppingCar1 = new ShoppingCar();
                shoppingCar1.setId(resultSet.getInt("id"));
                shoppingCar1.setUserId(resultSet.getInt("user_id"));
                shoppingCar1.setBookId(resultSet.getInt("book_id"));
                shoppingCar1.setBookNum(resultSet.getInt("book_num"));
                shoppingCar1.setBookPrice(resultSet.getDouble("book_price"));
                shoppingCar1.setBookName(resultSet.getString("book_name"));
                shoppingCar1.setCreate_time(resultSet.getTime("create_time"));
                shoppingCarList.add(shoppingCar1);
            }
            /*返回查找到的购物车*/
            return shoppingCarList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public List<ShoppingCar> findObjByPage(ShoppingCar shoppingCar, int pageNum, int pageSize) {
        int start = (pageNum-1)*pageSize;
        List<ShoppingCar> shoppingCarList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from shoppingcar where 1 = 1 ");
        if (shoppingCar.getId()!= 0){
            sb.append(" and id = "+shoppingCar.getId());
        }
        if (shoppingCar.getUserId()!=0){
            sb.append("  , user_id = "+shoppingCar.getUserId());
        }
        if (shoppingCar.getBookId()!=0){
            sb.append("  , book_id = "+shoppingCar.getBookId());
        }
        if (shoppingCar.getBookNum()!=0){
            sb.append(" , book_num = "+shoppingCar.getBookNum());
        }
        if (shoppingCar.getBookPrice()!=0){
            sb.append(" , book_price = "+shoppingCar.getBookPrice());
        }
        if (shoppingCar.getBookName()!=null){
            sb.append("  , book_name = '%"+shoppingCar.getBookName()+"%' ");
        }
        sb.append(" limit "+ start +","+pageSize);

        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                ShoppingCar shoppingCar1 = new ShoppingCar();
                shoppingCar1.setId(resultSet.getInt("id"));
                shoppingCar1.setUserId(resultSet.getInt("user_id"));
                shoppingCar1.setBookId(resultSet.getInt("user_id"));
                shoppingCar1.setBookNum(resultSet.getInt("book_num"));
                shoppingCar1.setBookPrice(resultSet.getDouble("book_price"));
                shoppingCar1.setBookName(resultSet.getString("book_name"));
                shoppingCar1.setCreate_time(resultSet.getDate("create_time"));
                shoppingCarList.add(shoppingCar1);
            }
            /*返回查找到的购物车*/
            return shoppingCarList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }
}
